﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.OleDb;
using System.Data;

namespace Bussiness
{
    /// <summary>
    /// 统计报表类
    /// </summary>
    public class WebReport
    {
        /// <summary>
        /// 项目
        /// </summary>
        public string ParaDescription { get; set; }
        /// <summary>
        /// 单位
        /// </summary>
        public string Unit { get; set; }
        /// <summary>
        /// 当日值
        /// </summary>
        public string Duty { get; set; }
        /// <summary>
        /// 月累计
        /// </summary>
        public string MonthSum { get; set; }
        /// <summary>
        /// 年累计
        /// </summary>
        public string YearSum { get; set; }
        /// <summary>
        /// 早班
        /// </summary>
        public string MorningSum { get; set; }
        /// <summary>
        /// 中班
        /// </summary>
        public string AfterSum { get; set; }
        /// <summary>
        /// 晚班
        /// </summary>
        public string NighSum { get; set; }
        /// <summary>
        /// 一值月累计
        /// </summary>
        public string OneSum { get; set; }
        /// <summary>
        /// 二值月累计
        /// </summary>
        public string TwoSum { get; set; }
        /// <summary>
        /// 三值月累计
        /// </summary>
        public string ThreeSum { get; set; }
        /// <summary>
        /// 四值月累计
        /// </summary>
        public string FourSum { get; set; }
        /// <summary>
        /// 返回统计报表
        /// </summary>
        /// <param name="paraDesc"></param>
        /// <param name="date"></param>
        /// <returns></returns>
        public WebReport GetReport(string paraID, string date,string paraDesc,string paraUnit)
        {
            WebReport report = new WebReport();
            report.ParaDescription = paraDesc;
            report.Unit = paraUnit;
            report.Duty = GetBZValue(paraID, date, 0);
            report.MorningSum = GetBZValue(paraID, date, 1);
            report.AfterSum = GetBZValue(paraID, date, 2);
            report.NighSum = GetBZValue(paraID, date, 3);
            report.OneSum = GetDuty(paraID, date, 1);
            report.TwoSum = GetDuty(paraID, date, 2);
            report.ThreeSum = GetDuty(paraID, date, 3);
            report.FourSum = GetDuty(paraID, date, 4);
            report.MonthSum = GetDuty(paraID, date, 0);
            report.YearSum = GetYearDuty(paraID, date, 0);


            return report;

        }

        public WebReport GetUpdateReport(string paraID, string date, string paraDesc, string paraUnit)
        {
            WebReport report = new WebReport();
            report.ParaDescription = paraDesc;
            report.Unit = paraUnit;
            string formatDate=date+" 00:00:00.0";
            string initMonth = GetInitMonth(date);
            System.Text.StringBuilder Sbuilder = new StringBuilder();
            Sbuilder.Append("select a.daystat,a.monthstat,a.yearstat,b.daystat,c.daystat,d.daystat,e.monthstat,f.monthstat,g.monthstat,h.monthstat from ");
            Sbuilder.Append(string.Format("(SELECT paraid,statdate,daystat,monthstat,yearstat FROM ADMINISTRATOR.STATREPORT where paraid='{0}' and statdate='{1}' and shift=0 ) a left join",paraID,formatDate));
            Sbuilder.Append(string.Format("(SELECT daystat,paraid,statdate FROM ADMINISTRATOR.STATREPORT where paraid='{0}' and statdate='{1}' and shift=1) b on a.paraid=b.paraid and a.statdate=b.statdate left join ",paraID,formatDate));
            Sbuilder.Append(string.Format("(SELECT daystat,paraid,statdate FROM ADMINISTRATOR.STATREPORT where paraid='{0}' and statdate='{1}' and shift=2) c on c.paraid=a.paraid and c.statdate=a.statdate left join ",paraID,formatDate));
            Sbuilder.Append(string.Format("(SELECT daystat,paraid,statdate FROM ADMINISTRATOR.STATREPORT where paraid='{0}' and statdate='{1}' and shift=3) d on d.paraid=a.paraid and d.statdate=a.statdate left join ",paraID,formatDate));
            Sbuilder.Append(string.Format("(SELECT monthstat,paraid,statdate FROM ADMINISTRATOR.STATREPORT where paraid='{0}' and statdate between '{1}' and '{2}' and duty=1 order by statdate desc fetch first 1 rows only) e  on e.paraid=a.paraid left join ",paraID,initMonth,formatDate));
            Sbuilder.Append(string.Format("(SELECT monthstat,paraid,statdate FROM ADMINISTRATOR.STATREPORT where paraid='{0}' and statdate between '{1}' and '{2}' and duty=2 order by statdate desc fetch first 1 rows only) f  on f.paraid=a.paraid left join ",paraID,initMonth,formatDate));
            Sbuilder.Append(string.Format("(SELECT monthstat,paraid,statdate FROM ADMINISTRATOR.STATREPORT where paraid='{0}' and statdate between '{1}' and '{2}' and duty=3 order by statdate desc fetch first 1 rows only) g  on g.paraid=a.paraid left join ",paraID,initMonth,formatDate));
            Sbuilder.Append(string.Format("(SELECT monthstat,paraid,statdate FROM ADMINISTRATOR.STATREPORT where paraid='{0}' and statdate between '{1}' and '{2}' and duty=4 order by statdate desc fetch first 1 rows only) h  on h.paraid=a.paraid", paraID, initMonth, formatDate));

            DB2Provider.DataLink link = new DB2Provider.DataLink();
            DataTable dt = link.ExcuteRetureTable(Sbuilder.ToString());
            object day=null;
            object month=null;
            object year=null;
            object monring=null;
            object after=null;
            object night=null;
            object one=null;
            object two=null;
            object three=null;
            object four=null;

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    day = dr[0];
                    month = dr[1];
                    year = dr[2];
                    monring = dr[3];
                    after = dr[4];
                    night = dr[5];
                    one = dr[6];
                    two = dr[7];
                    three = dr[8];
                    four = dr[9];

                    if (day != null && day != DBNull.Value)
                    {
                        //if (Convert.ToDouble(day).ToString("0.00") == "-9999.00")
                        //    report.Duty = "坏点";
                        //else if (Convert.ToDouble(day).ToString("0.00") == "-9998.00")
                        //    report.Duty = "停机";
                        //else
                            report.Duty = Convert.ToDouble(day).ToString("0.00");
                    }

                    if (month != null && month != DBNull.Value)
                    {
                        //if (Convert.ToDouble(month).ToString("0.00") == "-9999.00")
                        //    report.MonthSum = "坏点";
                        //else if (Convert.ToDouble(month).ToString("0.00") == "-9998.00")
                        //    report.MonthSum = "停机";
                        //else
                            report.MonthSum = Convert.ToDouble(dr[1]).ToString("0.00");
                    }

                    if (year != null && year != DBNull.Value)
                    {
                        //if (Convert.ToDouble(dr[2]).ToString("0.00") == "-9999.00")
                        //    report.YearSum = "坏点";
                        //else if (Convert.ToDouble(dr[2]).ToString("0.00") == "-9998.00")
                        //    report.YearSum = "停机";
                        //else
                            report.YearSum = Convert.ToDouble(year).ToString("0.00");
                    }

                    if (monring != null && monring != DBNull.Value)
                    {
                        //if (Convert.ToDouble(dr[3]).ToString("0.00") == "-9999.00")
                        //    report.MorningSum = "坏点";
                        //else if (Convert.ToDouble(dr[3]).ToString("0.00") == "-9998.00")
                        //    report.MorningSum = "停机";
                        //else
                        report.MorningSum = Convert.ToDouble(monring).ToString("0.00");
                    }

                    if (after != null && after != DBNull.Value)
                    {
                        //if (Convert.ToDouble(dr[4]).ToString("0.00") == "-9999.00")
                        //    report.AfterSum = "坏点";
                        //else if (Convert.ToDouble(dr[4]).ToString("0.00") == "-9998.00")
                        //    report.AfterSum = "停机";
                        //else
                            report.AfterSum = Convert.ToDouble(after).ToString("0.00");
                    }

                    if (night != null && night != DBNull.Value)
                    {
                        //if (Convert.ToDouble(dr[5]).ToString("0.00") == "-9999.00")
                        //    report.NighSum = "坏点";
                        //else if (Convert.ToDouble(dr[5]).ToString("0.00") == "-9998.00")
                        //    report.NighSum = "停机";
                        //else
                            report.NighSum = Convert.ToDouble(night).ToString("0.00");
                    }

                    if (one != null && one != DBNull.Value)
                    {
                        //if (Convert.ToDouble(dr[6]).ToString("0.00") == "-9999.00")
                        //    report.OneSum = "坏点";
                        //else if (Convert.ToDouble(dr[6]).ToString("0.00") == "-9998.00")
                        //    report.OneSum = "停机";
                        //else
                            report.OneSum = Convert.ToDouble(one).ToString("0.00");
                    }

                    if (two != null && two != DBNull.Value)
                    {
                        //if (Convert.ToDouble(dr[7]).ToString("0.00") == "-9999.00")
                        //    report.TwoSum = "坏点";
                        //else if (Convert.ToDouble(dr[7]).ToString("0.00") == "-9998.00")
                        //    report.TwoSum = "停机";
                        //else
                            report.TwoSum = Convert.ToDouble(two).ToString("0.00");
                    }

                    if (three != null && three != DBNull.Value)
                    {
                        //if (Convert.ToDouble(dr[8]).ToString("0.00") == "-9999.00")
                        //    report.ThreeSum = "坏点";
                        //else if (Convert.ToDouble(dr[8]).ToString("0.00") == "-9998.00")
                        //    report.ThreeSum = "停机";
                        //else
                            report.ThreeSum = Convert.ToDouble(three).ToString("0.00");
                    }

                    if (four != null && four != DBNull.Value)
                    {
                        //if (Convert.ToDouble(dr[9]).ToString("0.00") == "-9999.00")
                        //    report.FourSum = "坏点";
                        //else if (Convert.ToDouble(dr[9]).ToString("0.00") == "-9998.00")
                        //    report.FourSum = "停机";
                        //else
                            report.FourSum = Convert.ToDouble(four).ToString("0.00");
                    }
                }
            }
            //report.Duty = GetBZValue(paraID, date, 0);
            //report.MorningSum = GetBZValue(paraID, date, 1);
            //report.AfterSum = GetBZValue(paraID, date, 2);
            //report.NighSum = GetBZValue(paraID, date, 3);
            //report.OneSum = GetDuty(paraID, date, 1);
            //report.TwoSum = GetDuty(paraID, date, 2);
            //report.ThreeSum = GetDuty(paraID, date, 3);
            //report.FourSum = GetDuty(paraID, date, 4);
            //report.MonthSum = GetDuty(paraID, date, 0);
            //report.YearSum = GetYearDuty(paraID, date, 0);

            return report;
        }
        /// <summary>
        /// 判断参数报表是否存在
        /// </summary>
        /// <param name="paraID">参数ID</param>
        /// <returns></returns>
        public bool GetExistReport(string paraID)
        {
            try
            {
                string sql = "SELECT * FROM ADMINISTRATOR.STATREPORT WHERE PARAID='" + paraID + "'";
                DB2Provider.DataLink link = new DB2Provider.DataLink();
                DataTable dt = link.ExcuteRetureTable(sql);
                if (dt.Rows.Count == 0)
                    return false;
                else
                    return true;
            }
            catch (Exception ex)
            {
                throw new Exception("DB2数据库被用户占用，请先关闭DB2数据连接！");
            }
            finally
            {
                
            }
        }
        /// <summary>
        /// 返回参数描述结果集
        /// </summary>
        /// <returns></returns>
        public DataTable GetDescription()
        {
            string sql = "SELECT DISTINCT(PARADESC) FROM ADMINISTRATOR.STATREPORT";
            DB2Provider.DataLink link = new DB2Provider.DataLink();
            return link.ExcuteRetureTable(sql);
        }
        /// <summary>
        /// 返回单位
        /// </summary>
        /// <param name="paraDesc"></param>
        /// <returns></returns>
        public string GetUnit(string paraID)
        {
            string sql = "SELECT UNIT FROM ADMINISTRATOR.STATPARA WHERE PARAID='" + paraID + "'";
            DB2Provider.DataLink link = new DB2Provider.DataLink();
            return link.GetFirstValue(sql).ToString();

        }
        /// <summary>
        /// 获取当日、早、中、晚班值
        /// </summary>
        /// <param name="paraDesc"></param>
        /// <param name="date"></param>
        /// <param name="BZ"></param>
        /// <returns></returns>
        public string GetBZValue(string paraID, string date, int shift)
        {
            string sql = "SELECT DayStat FROM ADMINISTRATOR.STATREPORT WHERE PARAID='" + paraID + "' AND DATE(STATDATE)='" + date + "' AND SHIFT=" + shift + "";

            DB2Provider.DataLink link = new DB2Provider.DataLink();
            string dayValue = Convert.ToDouble(link.GetFirstValue(sql)).ToString("0.00");
            if (dayValue == "-9999.00")
                dayValue = "坏点";
            else if(dayValue == "-9998.00")
                dayValue = "停机";
            return dayValue;
        }
        /// <summary>
        /// 返回月累计、班值月累计
        /// </summary>
        /// <param name="paraDesc"></param>
        /// <param name="date"></param>
        /// <param name="duty"></param>
        /// <returns></returns>
        public string GetDuty(string paraID, string date, int duty)
        {
            string sql = "SELECT MonthStat FROM ADMINISTRATOR.STATREPORT WHERE PARAID='" + paraID + "' AND DATE(STATDATE)='" + date + "' AND DUTY=" + duty;

            DB2Provider.DataLink link = new DB2Provider.DataLink();

            string monthValue = Convert.ToDouble(link.GetFirstValue(sql)).ToString("0.00");

            if (monthValue == "0.00")
            {
                string Csql = "SELECT MonthStat FROM ADMINISTRATOR.STATREPORT WHERE PARAID='" + paraID + "' AND DATE(STATDATE)<'" + date + "' AND DATE(STATDATE)>='" + GetFirstMonth(date) + "' AND DUTY=" + duty + " ORDER BY STATDATE DESC";
                DB2Provider.DataLink Clink = new DB2Provider.DataLink();
                monthValue = Convert.ToDouble(Clink.GetFirstValue(Csql)).ToString("0.00");
            }
            if (monthValue == "-9999.00")
                monthValue = "坏点";
            else if (monthValue == "-9998.00")
                monthValue = "停机";
            return monthValue;

        }
        /// <summary>
        /// 返回月初
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        private string GetFirstMonth(string date)
        {
            return date.Substring(0, 8) + "01";
        }
        /// <summary>
        /// 返回月初
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        private string GetInitMonth(string date)
        {
            return date.Substring(0, 8) + "01 00:00:00.0";
        }
        
        /// <summary>
        /// 返回年累计
        /// </summary>
        /// <param name="paraDesc"></param>
        /// <param name="date"></param>
        /// <param name="duty"></param>
        /// <returns></returns>
        public string GetYearDuty(string paraID, string date, int shift)
        {
            string year="";
            string sql = "SELECT YearStat FROM ADMINISTRATOR.STATREPORT WHERE PARAID='" + paraID + "' AND DATE(STATDATE)='" + date + "' AND SHIFT=" + shift + "";

            DB2Provider.DataLink link = new DB2Provider.DataLink();
            object v = link.GetFirstValue(sql);
            if (v != null && v.ToString() != null && v.ToString() != "")
                year = Convert.ToDouble(v).ToString("0.00");
            if (year == "-9999.00")
                year = "坏点";
            else if (year == "-9998.00")
                year = "停机";
            return year;

        }
    }
}
